﻿/*
* This software is the confidential and proprietary
* information of NetBay
* Such confidential information should not be disclosed
* NetBay copyright(c) 
*/
#region Using
using System;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using NetBay.Core.DataAccess;
using NetBay.Core.Extensions;
using NetBay.Core.Log;
using Rio.Framework.RioManager;
using NetBay.Core.BusinessEntities.Versioning;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.IO;
using System.Collections.Generic;
using NetBay.Core.BusinessEntities;

#endregion

namespace Rio.Doc.DataAccess
{
    public class DA_Document
    {
        #region Enum

        /// <summary>
        /// List of DB functions to access data
        /// </summary>
        private enum DbFunction
        {
            [DescriptionAttribute("P_DOCUMENT_Rank_Update")]
            UpdateDocumentRank,
            [DescriptionAttribute("P_DOCUMENT_Item_Update")]
            UpdateDocumentItem,
            [DescriptionAttribute("P_DOCUMENT_Get_ById")]
            GetDocumentById,
            [DescriptionAttribute("P_MESSAGE_FLOW_AllDocument_Get")]
            GetDocumentByMessageFlow,
            [DescriptionAttribute("P_DOCUMENT_VERS_Get")]
            GetVersion,
            [DescriptionAttribute("P_DOCUMENT_VERS_Get_ByDocId")]
            GetAllVersionDocumentById,
            [DescriptionAttribute("P_DOCUMENT_VERS_Add")]
            AddVersion,
            [DescriptionAttribute("P_DOCUMENT_VERS_Add_FromOld")]
            AddVersionFromOld,
            [DescriptionAttribute("P_DOCUMENT_VERS_Add_Overwrite")]
            OverwriteVersion,
            [DescriptionAttribute("P_DOCUMENT_VERS_UpdateState")]
            ChangeVersionStateById,
            [DescriptionAttribute("P_DOCUMENT_VERS_GetCurrent")]
            GetCurrentVerison,
            [DescriptionAttribute("P_DOCUMENT_VERS_GetLast")]
            GetLastVersion,
            [DescriptionAttribute("P_DOCUMENT_VERS_Delete")]
            DeleteVersion,
            [DescriptionAttribute("P_DOCUMENT_Paste")]
            PasteDocument,
            [DescriptionAttribute("P_DOCUMENT_Get_ByFolderId")]
            GetDocumentByFolder,
            [DescriptionAttribute("P_DOCUMENT_DELETED_Get_ByFld")]
            GetDeletedDocumentByFolder,
            [DescriptionAttribute("P_DOCUMENT_Get_ByRdImage")]
            GetDocumentByRdImage,
            [DescriptionAttribute("P_DOCUMENT_Delete")]
            DeleteDocument,
            [DescriptionAttribute("P_DOCUMENT_Restore")]
            RestoreDocument,
            [DescriptionAttribute("P_DOCUMENT_Add")]
            AddDocument,
            [DescriptionAttribute("P_DOCUMENT_Remove")]
            DeleteRemove,
            [DescriptionAttribute("P_DOCUMENT_Update")]
            UpdateDocument,
            [DescriptionAttribute("P_DOCUMENT_Search")]
            SearchDocument,
            [DescriptionAttribute("P_DOCUMENT_MessageFlow_Add")]
            AddMessageFlowDocument,
            [DescriptionAttribute("P_DOCUMENT_FullText_Update")]
            UpdateDocumentForFullText,
            [DescriptionAttribute("P_DOCUMENT_Rank_Get")]
            GetDocumentRank,
            [DescriptionAttribute("P_DOCUMENT_Rank_Move")]
            MoveDocumentRank,
            [DescriptionAttribute("P_MESSAGE_FLOW_Histo_ByState")]
            GetDocumentByHistoState,
        }

        private enum FullTextMode
        {
            /// <summary>
            /// Nothing to do
            /// </summary>
            None = 0,
            /// <summary>
            /// FullText request done
            /// </summary>
            Done,
            /// <summary>
            /// Request a fullText
            /// </summary>
            Request,
            /// <summary>
            /// Error while FullTextJob
            /// </summary>
            Error,
        }

        #endregion

        #region Common

        /// <summary>
        /// Get the number of document
        /// </summary>
        /// <returns>documents count</returns>
        public static IDataReader GetNbDocument()
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetSqlStringCommand("select count(rd_no) as NbDoc from  r_doc");

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive the document by its 'rd_idimage'
        /// </summary>
        /// <param name="p_lUserId">The P_L user id.</param>
        /// <param name="p_szRdImage">rd_idimage</param>
        /// <param name="p_lFoldertypId">The P_L foldertyp id.</param>
        /// <returns>
        /// DataReader with the document
        /// </returns>
        public static IDataReader GetDocumentByRdImage(long p_lUserId, string p_szRdImage, long p_lFoldertypId)
        {
            LogManager.Instance().LogDebug("(p_szRdImage<" + p_szRdImage + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentByRdImage.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFoldertypId);
            db.AddInParameter(dbCommand, "p_sRdImage", DbType.String, p_szRdImage);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive documents by Folder
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lFolderTypeId">FolderType ID</param>
        /// <param name="p_lFolderId">Folder ID</param>
        /// <returns>
        /// DataReader with documents
        /// </returns>
        public static IDataReader GetDocumentByFolder(long p_lUserId, long p_lFolderTypeId, long p_lFolderId)
        {
            LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + "> p_lFolderId<" + p_lFolderId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentByFolder.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive deleted documents by Folder
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lFolderTypeId">FolderType ID</param>
        /// <param name="p_lFolderId">Folder ID</param>
        /// <returns>
        /// DataReader with documents
        /// </returns>
        public static IDataReader GetDeletedDocumentByFolder(long p_lUserId, long p_lFolderTypeId, long p_lFolderId)
        {
            LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + "> p_lFolderId<" + p_lFolderId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDeletedDocumentByFolder.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Search documents by Note and CreditPhoto
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lFolderTypeId">FolderType identifier</param>
        /// <param name="p_szNote">regular expression applied into document's note</param>
        /// <param name="p_szCreditPhoto">regular expression applied into creditPhoto's note</param>
        /// <param name="p_szAuteur">The P_SZ auteur.</param>
        /// <param name="p_szFichierOrigine">The P_SZ fichier origine.</param>
        /// <returns>
        /// DataReader with documents
        /// </returns>
        public static IDataReader SearchDocuments(long p_lUserId, long p_lFolderTypeId, string p_szNote = null, string p_szCreditPhoto = null, string p_szAuteur = null, string p_szFichierOrigine = null)
        {
            try
            {

                LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + "> p_szNote<" + p_szNote + "> p_szCreditPhoto<" + p_szCreditPhoto + " p_szAuteur<" + p_szAuteur + "> p_szFichierOrigine<" + p_szFichierOrigine + ">)...");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.SearchDocument.szGetDescription());

                // Replace '*' by '%' to match in syntax SQL
                if (!string.IsNullOrEmpty(p_szNote))
                {
                    p_szNote = p_szNote.Replace('*', '%');
                }
                if (!string.IsNullOrEmpty(p_szCreditPhoto))
                {
                    p_szCreditPhoto = p_szCreditPhoto.Replace('*', '%');
                }
                if (!string.IsNullOrEmpty(p_szAuteur))
                {
                    p_szAuteur = p_szAuteur.Replace('*', '%');
                }
                if (!string.IsNullOrEmpty(p_szFichierOrigine))
                {
                    p_szFichierOrigine = p_szFichierOrigine.Replace('*', '%');
                }

                // By default "" is %
                p_szNote = DefaultSearchField(p_szNote);
                p_szCreditPhoto = DefaultSearchField(p_szCreditPhoto);
                p_szAuteur = DefaultSearchField(p_szAuteur);
                p_szFichierOrigine = DefaultSearchField(p_szFichierOrigine);

                db.AddInParameter(dbCommand, "p_note", DbType.String, p_szNote);
                db.AddInParameter(dbCommand, "p_creditPhoto", DbType.String, p_szCreditPhoto);
                db.AddInParameter(dbCommand, "p_auteur", DbType.String, p_szAuteur);
                db.AddInParameter(dbCommand, "p_fichier_origine", DbType.String, p_szFichierOrigine);
                db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
                db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);

                return db.ExecuteReader(dbCommand);
            }
            catch (Exception ex)
            {
                NetBay.Core.Log.LogManager.Instance().LogError("SearchDocuments Exception [" + ex.ToString() + "]");
            }
            return null;
        }

        /// <summary>
        /// Defaults the search field.
        /// </summary>
        /// <param name="p_szNote">The note.</param>
        /// <returns></returns>
        private static string DefaultSearchField(string p_szNote)
        {
            if (string.IsNullOrWhiteSpace(p_szNote) == true)
                p_szNote = "%";
            return p_szNote;
        }

        /// <summary>
        /// Add a document in our EDM
        /// </summary>
        /// <param name="p_lFolderTypeId">FolderType ID</param>
        /// <param name="p_lFolderId">Folder ID</param>
        /// <param name="p_szIdDoc">Id Doc</param>
        /// <param name="p_szSourceDocumentName">Original document name</param>
        /// <param name="p_szDocumentName">Document name into our EDM</param>
        /// <param name="p_szFolderPath">Path to reach our document in our EDM</param>
        /// <param name="p_szItem">Code for Item where the document is associated</param>
        /// <param name="p_szNote">Note associated at document</param>
        /// <param name="p_szAuthor">Author of document</param>
        /// <param name="p_szPhotoCredit">Credit for the photo</param>
        /// <param name="p_szVideoLenght">Video Lenght</param>
        /// <param name="p_bFullTextRequested">Indicate if a fulltext is requested for this document</param>
        /// <param name="p_szIdCourrierErp">The P_SZ id courrier erp.</param>
        /// <param name="p_szMesureErp">The P_SZ mesure erp.</param>
        /// <returns>
        /// DocumentID
        /// </returns>
        public static long AddDocument(long p_lFolderTypeId, long p_lFolderId, string p_szIdDoc,
            string p_szSourceDocumentName, string p_szDocumentName, string p_szFolderPath, string p_szItem,
            string p_szNote, string p_szAuthor, string p_szPhotoCredit, string p_szVideoLenght, bool p_bFullTextRequested,
            string p_szIdCourrierErp = "", string p_szMesureErp = ""
            )
        {
            LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + "> p_lFolderId<" + p_lFolderId + "> p_szIdDoc<" + p_szIdDoc + "> p_szSourceDocumentName<" +
                p_szSourceDocumentName + "> p_szDocumentName<" + p_szDocumentName + "> p_szFolderPath<" + p_szFolderPath + "> p_szItem<" + p_szItem +
                "> p_szNote<" + p_szNote + "> p_szAuthor<" + p_szAuthor + "> p_szPhotoCredit<" + p_szPhotoCredit + "> p_szVideoLenght<" + p_szVideoLenght +
                "> p_bFullTextRequested<" + p_bFullTextRequested +
                "> p_szIdCourrierErp<" + p_szIdCourrierErp + "> p_szMesureErp<" + p_szMesureErp + "> )...");
            long l_bRet = 0;

            // Determine the fulltext parameter
            FullTextMode l_tFullTextMode = FullTextMode.None;
            Database db = DbHelper.DataBase;

            using (var dbCommand = db.GetNewCommand(DbFunction.AddDocument.szGetDescription()))
            {

                db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);                            // 3
                db.AddInParameter(dbCommand, "p_sDocumentName", DbType.String, p_szDocumentName);                   // 20100426614*******_US89.tif
                db.AddInParameter(dbCommand, "p_sItem", DbType.String, p_szItem);                                   // 001002000000
                db.AddInParameter(dbCommand, "p_sFolderPath", DbType.String, p_szFolderPath);                       // 20\2010\04\26\14\30\41
                db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);                    // 78
                db.AddInParameter(dbCommand, "p_sNote", DbType.String, p_szNote);                                   // Chèque pour la facturation
                db.AddInParameter(dbCommand, "p_sAuthor", DbType.String, p_szAuthor);                               // Picasso
                db.AddInParameter(dbCommand, "p_sPhotoCredit", DbType.String, p_szPhotoCredit);                     // Figaro magazine
                db.AddInParameter(dbCommand, "p_sVideoLength", DbType.String, p_szVideoLenght);                     // 1h 20mn 37s
                db.AddInParameter(dbCommand, "p_sSourceDocumentName", DbType.String, Path.GetFileName(p_szSourceDocumentName));   // MonChatSurLAbre.jpg
                db.AddInParameter(dbCommand, "p_sIdDoc", DbType.String, p_szIdDoc);                                 // 20100426614***
                db.AddInParameter(dbCommand, "p_sFullText", DbType.String, (int)l_tFullTextMode);                   // Mode FullText
                db.AddInParameter(dbCommand, "p_sIdCourrierErp", DbType.String, p_szIdCourrierErp);                 // CourrierID de ERP
                db.AddInParameter(dbCommand, "p_sMesureErp", DbType.String, p_szMesureErp);                         // Mesure de ERP


                using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
                {
                    while (userDataReader.Read())
                    {
                        l_bRet = Convert.ToInt64(userDataReader["RD_NO"]);
                    }
                }
            }

            return l_bRet;
        }

        /// <summary>
        /// Adds the document.
        /// </summary>
        /// <param name="p_oDocumentList">The document list.</param>
        /// <param name="p_bInsertMode">if set to <c>true</c> [P_B insert mode].</param>
        /// <returns></returns>
        public static List<Document> AddDocument(List<Document> p_oDocumentList, bool p_bInsertMode)
        {
            List<Document> l_bRet = new List<Document>();
            // Determine the fulltext parameter
            FullTextMode l_tFullTextMode = FullTextMode.None;
            Database db = DbHelper.DataBase;

            int l_iLatestDocumentRank = -1;
            if (!p_bInsertMode)
            {
                l_iLatestDocumentRank = GetLatestRank(p_oDocumentList[0].FolderId, p_oDocumentList[0].FolderTypeIdentifier);
            }
            else
            {
                MoveDocumentRank(p_oDocumentList.Count, p_oDocumentList[0].FolderId, p_oDocumentList[0].FolderTypeIdentifier);
            }

            // int l_iLatestDocumentRank = GetLatestRank(p_oDocumentList[0].FolderId);
            l_iLatestDocumentRank++;
            foreach (Document l_od in p_oDocumentList)
            {
                using (var dbCommand = db.GetNewCommand(DbFunction.AddDocument.szGetDescription()))
                {
                    db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, l_od.FolderId); // 3
                    db.AddInParameter(dbCommand, "p_sDocumentName", DbType.String, l_od.ImageName);
                    db.AddInParameter(dbCommand, "p_sItem", DbType.String, l_od.ItemRank); // 001002000000
                    db.AddInParameter(dbCommand, "p_sFolderPath", DbType.String, l_od.RelativePath);
                    db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, l_od.FolderTypeIdentifier); // 78
                    db.AddInParameter(dbCommand, "p_sNote", DbType.String, l_od.Note); // Chèque pour la facturation
                    db.AddInParameter(dbCommand, "p_sAuthor", DbType.String, l_od.Author); // Picasso
                    db.AddInParameter(dbCommand, "p_sPhotoCredit", DbType.String, l_od.PhotoCredit);
                    db.AddInParameter(dbCommand, "p_sVideoLength", DbType.String, l_od.VideoLength); // 1h 20mn 37s
                    db.AddInParameter(dbCommand, "p_sSourceDocumentName", DbType.String, Path.GetFileName(l_od.OriginalNameFile)); // MonChatSurLAbre.jpg
                    db.AddInParameter(dbCommand, "p_sIdDoc", DbType.String, l_od.FolderIdentifier);
                    db.AddInParameter(dbCommand, "p_sFullText", DbType.String, (int) l_tFullTextMode);
                    db.AddInParameter(dbCommand, "p_iRank", DbType.Int32, l_iLatestDocumentRank++);
                    db.AddInParameter(dbCommand, "p_sIdCourrierErp", DbType.String, l_od.IdCourrierErp);
                    db.AddInParameter(dbCommand, "p_sMesureErp", DbType.String, l_od.MesureErp); //Mesure ERP

                    using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
                    {
                        userDataReader.Read();
                        var l_oCurrentDoc = new Document();
                        l_oCurrentDoc.Identifier = Convert.ToInt64(userDataReader[0]);
                        l_oCurrentDoc.ImageName = l_od.OriginalNameFile;
                        l_oCurrentDoc.RelativePath =
                            Path.Combine(SettingsManager.cStoreSettings.Parameter_Storage, l_od.RelativePath) + @"\" +
                            l_od.ImageName;
                        l_oCurrentDoc.MultiVolumePath =
                            Path.ChangeExtension(
                                Path.Combine(SettingsManager.cStoreSettings.Thumbnail_Directory, l_od.RelativePath) +
                                @"\" + l_od.ImageName, Constant.ImageFileFormat.JPG.szGetDescription());
                        l_oCurrentDoc.FolderIdentifier = l_od.FolderIdentifier;
                        l_oCurrentDoc.FolderId = l_od.FolderId;
                        l_bRet.Add(l_oCurrentDoc);

                        userDataReader.Close();
                    }
                }
            }

            return l_bRet;
        }

        /// <summary>
        /// Moves the document rank.
        /// </summary>
        /// <param name="p_iDelta">The delta.</param>
        /// <param name="p_lFolderId">The folder id.</param>
        /// <param name="p_lFolderTypeId">The P_L folder type id.</param>
        private static void MoveDocumentRank(int p_iDelta, long p_lFolderId, long p_lFolderTypeId)
        {
            Database db = DbHelper.DataBase;
            var dbCommand = db.GetNewCommand(DbFunction.MoveDocumentRank.szGetDescription(), false);

            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);
            db.AddInParameter(dbCommand, "p_iNbDocInserted", DbType.Int16, p_iDelta);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Get latest document Rank
        /// </summary>
        /// <param name="p_lFolderId">The P_L folder id.</param>
        /// <param name="p_lFolderTypeId">The P_L folder type id.</param>
        /// <returns></returns>
        private static int GetLatestRank(long p_lFolderId, long p_lFolderTypeId)
        {
            int l_iRank = 0;
            Database db = DbHelper.DataBase;
            using (var dbCommand = db.GetNewCommand(DbFunction.GetDocumentRank.szGetDescription()))
            {
                db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);
                db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
                                     
                using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
                {
                    userDataReader.Read();
                    l_iRank = Convert.ToInt32(userDataReader[0]);
                }
            }
            return l_iRank;
        }



        /// <summary>
        /// Add a messageFlow in our EDM
        /// </summary>
        /// <param name="p_szSourceDocumentName">Original document name</param>
        /// <param name="p_szDocumentName">Document name into our EDM</param>
        /// <param name="p_szFolderPath">Path to reach our document in our EDM</param>
        /// <param name="p_szNote">Note associated at document</param>
        /// <param name="p_szAuthor">Author of document</param>
        /// <param name="p_szPhotoCredit">Credit for the photo</param>
        /// <param name="p_szVideoLenght">Video Lenght</param>
        /// <returns>DocumentID</returns>
        public static long AddDocumentMessageFlow(string p_szSourceDocumentName, string p_szDocumentName, string p_szFolderPath,
            string p_szNote, string p_szAuthor, string p_szPhotoCredit, string p_szVideoLenght
            )
        {
            LogManager.Instance().LogDebug("(p_szSourceDocumentName<" + p_szSourceDocumentName + "> p_szDocumentName<" + p_szDocumentName + "> p_szFolderPath<" + p_szFolderPath +
                "> p_szNote<" + p_szNote + "> p_szAuthor<" + p_szAuthor + "> p_szPhotoCredit<" + p_szPhotoCredit + "> p_szVideoLenght<" + p_szVideoLenght + ">)...");
            long l_bRet = 0;
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddMessageFlowDocument.szGetDescription());

            db.AddInParameter(dbCommand, "p_sDocumentName", DbType.String, p_szDocumentName);               // 20100426614*******_US89.tif
            db.AddInParameter(dbCommand, "p_sFolderPath", DbType.String, p_szFolderPath);                   // 20\2010\04\26\14\30\41
            db.AddInParameter(dbCommand, "p_sNote", DbType.String, p_szNote);                               // Chèque pour la facturation
            db.AddInParameter(dbCommand, "p_sAuthor", DbType.String, p_szAuthor);                           // Picasso
            db.AddInParameter(dbCommand, "p_sPhotoCredit", DbType.String, p_szPhotoCredit);                 // Figaro magazine
            db.AddInParameter(dbCommand, "p_sVideoLength", DbType.String, p_szVideoLenght);                 // 1h 20mn 37s
            db.AddInParameter(dbCommand, "p_sSourceDocumentName", DbType.String, p_szSourceDocumentName);   // MonChatSurLAbre.jpg

            using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
            {
                while (userDataReader.Read())
                {
                    l_bRet = Convert.ToInt64(userDataReader["RD_NO"]);
                }
            }
            return l_bRet;
        }

        /// <summary>
        /// Delete a document 
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param>
        public static void DeleteDocument(long p_lDocumentId)
        {
            LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.DeleteDocument.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Restore a document 
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param>
        public static void RestoreDocument(long p_lDocumentId)
        {
            LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.RestoreDocument.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Remove a document 
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param>
        public static void RemoveDocument(long p_lDocumentId)
        {
            LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.DeleteRemove.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Update document's infos
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param>
        /// <param name="p_szNote">Note</param>
        /// <param name="p_szAuthor">Author</param>
        /// <param name="p_szPhotoCredit">Photo credit</param>
        public static void UpdateDocument(long p_lDocumentId, string p_szNote, string p_szAuthor, string p_szPhotoCredit)
        {
            LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + "> p_szNote<" + p_szNote + "> p_szAuthor<" + p_szAuthor + "> p_szPhotoCredit<" + p_szPhotoCredit + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.UpdateDocument.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int32, p_lDocumentId);
            db.AddInParameter(dbCommand, "p_sNote", DbType.String, p_szNote);
            db.AddInParameter(dbCommand, "p_sAuthor", DbType.String, p_szAuthor);
            db.AddInParameter(dbCommand, "p_sPhotoCredit", DbType.String, p_szPhotoCredit);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Update document's infos
        /// </summary>
        /// <param name="p_sDocumentList">The P_S document list.</param>
        public static void UpdateDocumentsForFullText(string p_sDocumentList)
        {
            LogManager.Instance().LogDebug("(p_sDocumentList<" + p_sDocumentList + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.UpdateDocumentForFullText.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lstRdNo", DbType.String, p_sDocumentList);
            db.AddInParameter(dbCommand, "p_iState", DbType.Int32, (int)FullTextMode.Request);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Retrive al messages sent by a user
        /// </summary>
        /// <param name="p_lMessageFlowId">The message flow identifier</param>
        /// <returns>All documents from a message flow</returns>
        public static IDataReader GetDocumentByMessageFlow(long p_lMessageFlowId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentByMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lMessageFlowId", DbType.Int32, p_lMessageFlowId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retrive al messages sent by a user
        /// </summary>
        /// <param name="p_lGroupId">The P_L group id.</param>
        /// <param name="p_lMsgFlowId">The P_L MSG flow id.</param>
        /// <param name="p_iFilteredByState">State of the p_i filtered by.</param>
        /// <returns>
        /// All documents from a message flow
        /// </returns>
        public static IDataReader GetDocumentByGroupMessageFlow(long p_lGroupId, long p_lMsgFlowId, int p_iFilteredByState)
        {
            var db = DbHelper.DataBase;


            var l_sPackageName = string.Empty;
            var l_bOutPutParameter = false;
            switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
            {
                case "Oracle":
                    l_bOutPutParameter = true;
                    l_sPackageName = SettingsManager.cStoreSettings.Parameter_OraclePackage;
                    break;

            }

            var dbCommand = db.GetStoredProcCommand(l_sPackageName + DbFunction.GetDocumentByHistoState.szGetDescription());
            db.AddInParameter(dbCommand, "p_lGroupId", DbType.Int64, p_lGroupId);
            db.AddInParameter(dbCommand, "p_lEtatId", DbType.Int32, p_iFilteredByState);
            db.AddInParameter(dbCommand, "p_lMsgFlowId", DbType.Int64, p_lMsgFlowId);


            if (l_bOutPutParameter)
            {
                var l_oOracleOutputParameter = new OracleParameter("O_Results", OracleType.Cursor);
                l_oOracleOutputParameter.Direction = ParameterDirection.Output;
                dbCommand.Parameters.Add(l_oOracleOutputParameter);
            }


            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Update documents rank
        /// </summary>
        /// <param name="p_sDocumentNewRank">The documents and thier new ranks. RD_NO;RANK;#RD_NO;RANK;#RD_NO;RANK;#RD_NO;RANK;#</param>
        /// <returns>
        /// True if success, false otherwise
        /// </returns>
        public static Boolean UpdateDocumentRank(string p_sDocumentNewRank)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(p_sDocumentNewRank<" + p_sDocumentNewRank + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.UpdateDocumentRank.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_sDocumentNewRank", DbType.String, p_sDocumentNewRank);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("DA_Document --> UpdateDocumentRank exception[" + ex.Message + "]");
            }
            return l_bReturn;
        }

        /// <summary>
        /// Update documents item
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param>
        /// <param name="p_szItem">Document Item</param>
        /// <returns>True if success, false otherwise</returns>
        public static Boolean UpdateDocumentItem(long p_lDocumentId, string p_szItem)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + "> p_szItem<" + p_szItem + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.UpdateDocumentItem.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);
                db.AddInParameter(dbCommand, "p_sItem", DbType.String, p_szItem);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("(p_lDocumentId<" + p_lDocumentId + "> p_szItem<" + p_szItem + ">) Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Retreive the documents by Document ID
        /// </summary>
        /// <param name="p_lDocumentId"></param>
        /// <returns>DataReader with document</returns>
        public static IDataReader GetDocumentById(long p_lDocumentId)
        {
            LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocumentId + ">)..."); 
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentById.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);

            return db.ExecuteReader(dbCommand);
        }

        #endregion

        #region Versioning

        /// <summary>
        /// Retreive all document version by Document ID.
        /// </summary>
        /// <param name="p_lDocId">The P_L doc id.</param>
        /// <returns>
        /// DataReader with document
        /// </returns>
        public static IDataReader GetAllVersionDocumentById(long p_lDocId)
        {
            LogManager.Instance().LogDebug("(p_lDocId <" + p_lDocId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetAllVersionDocumentById.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Adds the version.
        /// </summary>
        /// <param name="DocId">The doc id.</param>
        /// <param name="UserId">The user id.</param>
        /// <param name="FileName">Name of the file.</param>
        /// <param name="p_lModelId">The P_L model id.</param>
        /// <param name="IsMajor">The is major.</param>
        /// <returns></returns>
        public static IDataReader AddVersion(long DocId, long UserId, string FileName, long p_lModelId, bool IsMajor)
        {
            LogManager.Instance().LogDebug("(p_lDocId <" + DocId + ">p_lUserId <" + UserId + ")...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddVersion.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, DocId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, UserId);
            db.AddInParameter(dbCommand, "p_sFileName", DbType.String, FileName);
            db.AddInParameter(dbCommand, "p_bIsMajor", DbType.Int32, Convert.ToInt32(IsMajor));
            db.AddInParameter(dbCommand, "p_lModelId", DbType.Int64, p_lModelId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Add a version to the document from an old version
        /// </summary>
        /// <param name="p_lDocId">Document identifier</param>
        /// <param name="p_lUserId">User identifier</param>
        /// <param name="p_lOldVersionId">Old Version identifier</param>
        /// <param name="p_lModelId">The P_L model id.</param>
        /// <param name="p_bIsMajor">Indicate whether if the new version is a major</param>
        /// <returns></returns>
        public static IDataReader AddVersionFromOld(long p_lDocId, long p_lUserId, long p_lOldVersionId, long p_lModelId, bool p_bIsMajor)
        {
            LogManager.Instance().LogDebug("(p_lDocId <" + p_lDocId + ">p_lUserId <" + p_lUserId + "> p_lOldVersionId<" + p_lOldVersionId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddVersionFromOld.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_OldVersionId", DbType.Int64, p_lOldVersionId);
            db.AddInParameter(dbCommand, "p_bIsMajor", DbType.Int32, Convert.ToInt32(p_bIsMajor));
            db.AddInParameter(dbCommand, "p_lModelId", DbType.Int64, p_lModelId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Overwrite version a version
        /// </summary>
        /// <param name="p_lDocId">Document identifier</param>
        /// <param name="p_lUserId">User identifier</param>
        /// <param name="p_sFilename">New filename</param>
        /// <returns></returns>
        public static IDataReader OverwriteVersion(long p_lDocId, long p_lUserId, string p_sFilename)
        {
            LogManager.Instance().LogDebug("(p_lDocId <" + p_lDocId + ">p_lUserId <" + p_lUserId + ">p_sFilename <" + p_sFilename + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.OverwriteVersion.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_sFileName", DbType.String, p_sFilename);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Change version state
        /// </summary>
        /// <param name="VersionId">Version identifier</param>
        /// <param name="NewState">New state</param>
        /// <returns></returns>
        public static Boolean ChangeVersioningStateById(long VersionId, VersionState NewState)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(VersionId<" + VersionId + "> NewState<" + NewState.ToString() + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.ChangeVersionStateById.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lRdvmId", DbType.Int64, VersionId);
                db.AddInParameter(dbCommand, "p_lNewState", DbType.Int64, Convert.ToInt64(NewState));

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("(VersionId<" + VersionId + "> NewState<" + NewState.ToString() + ">) Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        /// <summary>
        /// Get a version by id
        /// </summary>
        /// <param name="p_lRdvId">Version identifier</param>
        /// <returns></returns>
        public static IDataReader GetVersion(long p_lRdvId)
        {
            LogManager.Instance().LogDebug("(p_lRdvId <" + p_lRdvId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetVersion.szGetDescription());
            db.AddInParameter(dbCommand, "p_lRdvId", DbType.Int64, p_lRdvId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the current version.
        /// </summary>
        /// <param name="DocId">The doc id.</param>
        /// <returns></returns>
        public static IDataReader GetCurrentVersion(long DocId)
        {
            LogManager.Instance().LogDebug("(DocId<" + DocId + ")");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetCurrentVerison.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, DocId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Gets the current version.
        /// </summary>
        /// <param name="DocId">The doc id.</param>
        /// <returns></returns>
        public static IDataReader GetLastVersion(long DocId)
        {
            LogManager.Instance().LogDebug("(DocId<" + DocId + ")");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetLastVersion.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, DocId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Undoes the checkout.
        /// </summary>
        /// <param name="VersionId">The version id.</param>
        /// <returns></returns>
        public static Boolean UndoCheckout(long VersionId)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(VersionId<" + VersionId + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.DeleteVersion.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lVersionId", DbType.Int64, VersionId);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("(VersionId<" + VersionId + ">) Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }

        #endregion

        #region Paste 

        /// <summary>
        /// Paste the document to the destination folder. The isCut determine the source action
        /// </summary>
        /// <param name="p_lDocId">List of document identifiers</param>
        /// <param name="p_sDestinationFolderIdentifier">The P_S destination folder identifier.</param>
        /// <param name="FolderIdentifier">The folder identifier.</param>
        /// <param name="p_sDestinationFolderTypeId">The P_S destination folder type id.</param>
        /// <param name="p_bisCut">if set to <c>true</c> [p_bis cut].</param>
        /// <param name="p_sItemRank">The P_S item rank.</param>
        /// <param name="p_bisOriginalVersion">if set to <c>true</c> [p_bis original version].</param>
        /// <param name="VersionId">The version id.</param>
        /// <returns></returns>
        public static Boolean PasteDocument(long p_lDocId, string p_sDestinationFolderIdentifier, long FolderIdentifier, long p_sDestinationFolderTypeId, bool p_bisCut, string p_sItemRank = null, bool p_bisOriginalVersion = false, long VersionId = 0)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(p_lDocumentId<" + p_lDocId + "> p_sDestinationFolderIdentifier<" + p_sDestinationFolderIdentifier + "> p_bisCut<" + p_bisCut + "> p_sItemRank<" + p_sItemRank + "> p_bisOldVersion<" + p_bisOriginalVersion.ToString() + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.PasteDocument.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);
                db.AddInParameter(dbCommand, "p_sDestinationFolderId", DbType.String, p_sDestinationFolderIdentifier);
                db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, FolderIdentifier);
                db.AddInParameter(dbCommand, "p_sDestinationFolderTypeId", DbType.String, p_sDestinationFolderTypeId.ToString());
                db.AddInParameter(dbCommand, "p_bisCut", DbType.Int32, Convert.ToInt32(p_bisCut));
                db.AddInParameter(dbCommand, "p_sItemRank", DbType.String, p_sItemRank);
                db.AddInParameter(dbCommand, "p_bisOriginalVersion", DbType.Int32, Convert.ToInt32(p_bisOriginalVersion));
                db.AddInParameter(dbCommand, "p_lVersionId", DbType.Int64, VersionId);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("(p_lDocumentId<" + p_lDocId + "> p_sDestinationFolderIdentifier<" + p_sDestinationFolderIdentifier + "> p_bisCut<" + p_bisCut + "> p_sItemRank<" + p_sItemRank + "> p_bisOldVersion<" + p_bisOriginalVersion.ToString() + ">) Exception -> " + Environment.NewLine + ex.Message);
            }
            return l_bReturn;
        }
        #endregion
    }
}